Excel 之中国式排序（排名连续） |
您所在的位置：网站首页 › 中国式排名公式 excel › Excel 之中国式排序（排名连续） |
Excel 之中国式排序（排名连续）
|
Excel 之中国式排序（排名连续） 本文内容主要参考自：https://www.sohu.com/a/234253447_99914465 中国式排序摘自：https://baike.baidu.com/item/%E4%B8%AD%E5%9B%BD%E5%BC%8F%E6%8E%92%E5%90%8D 在排名计算方法中，中国人的习惯是，无论有几个并列第 2 名，之后的排名仍应该是第 3 名，即并列排名不占用名次。 例如：对数列{3,2,2,1}从大到小进行排名，按国际通行的惯例是{第一名，第二名，第二名，第四名}，比如奥运会某场比赛出现并列银牌，则不发铜牌 而按中国式排名，结果是{第一名，第二名，第二名，第三名}。 使用 Excel 的公式实现中国式排序这里我们只考虑SUMPRODUCT+COUNTIF的公式方法，这或许是最直接且简单的形式了。 之所以不考虑其他形式是因为直接使用公式可以方便的迁移到条件格式中，用来自定义数据单元格的显示格式。
为了便于解释，这里首先引入我们的数据：
注意这里展示了四种实际可以看到的情形： A 列：正常数据，但是我们需要选择最小的前三个，分别使用红绿蓝色加粗标注。B 列：正常数据，但是我们需要选择最大的前三个，分别使用红绿蓝色加粗标注。C 列：存在缺失数据，但是我们仍然需要在剩下的数据集中选择最小的前三个，分别使用红绿蓝色加粗标注。D 列：存在缺失数据，但是我们仍然需要在剩下的数据集中选择最大的前三个，分别使用红绿蓝色加粗标注。组件函数的功能SUMPRODUCT
即对各个参数数组对应元素的乘积累和。 当 sumproduct 函数中的参数只有一个数组时，即对数组{1;2;3;4;5;6;7}进行求和，1+2+3+4+5+6+7=28。当 sumproduct 函数中参数为两个数组时，两个数组的所有元素对应相乘。 公式=sumproduct(A2:A8,B2:B8)可转化为=sumproduct(数组1,数组2)=sumproduct({1;2;3;4;5;6;7},{1;2;3;4;5;6;7})=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。当 sumproduct 函数中参数为三个数组时，三个数组的所有元素对应相乘。高级用法——条件求和： 单条件求和： =sumproduct((A2:A13="成都发货平台")*(B2:B13))用来统计成都发货平台的发货量。 看到这公式你可能有疑惑，它跟语法格式好像不一样，其实把它看做是只有一个参数。公式分解=sumproduct({数组1}*{数组2})=sumproduct({TRUE;…..TRUE;…..TRUE}*{11012;…41568;…12506})=1*11012+1*41568+1*12506=65086。sumproduct函数中，逗号分割的各个参数必须为数字型数据。如果是判断的结果逻辑值，就要*1转换为数字。如果不用逗号，直接用* 连接，就相当于乘法运算，就不必添加*1。所以以下两种形式等价： 一种就是=sumproduct((A2:A13="成都发货平台")*(B2:B13))，直接将逻辑数组乘以数字数组。另一种是=sumproduct((A2:A13="成都发货平台")*1, (B2:B13))，乘以 1，把它转化成数组才能参与运算。多条件求和： =SUMPRODUCT((A2:A13="成都发货平台")*(C2:C13="重庆发货平台")*(D2:D13))用来求当发货平台为成都，收货平台为重庆的发货量。求成都发货平台和重庆发货平台的发货量，只要求满足其中的一个条件时，可以使用=SUMPRODUCT(((A2:A13="成都发货平台")+(A2:A13="重庆发货平台")),(B2:B13))。注意：SUMPRODUCT(条件1*条件2*条件3...条件N)中 * ：满足所有条件(all);SUMPRODUCT(条件1+条件2+条件3...+条件N)中 + ：满足任一条件(any)。实用案例： 有条件计数 =SUMPRODUCT((A2:A13="成都发货平台")*(C2:C13="重庆发货平台"))利用 sumproduct 函数中 TRUE 和 FALSE 逻辑数组 1 和 0 的特效来计数。公式分解=sumproduct({1;0;0;0;0;0;0;0;0;1;0;1}*{1;0;0;0;0;0;1;0;0;1;0;0})=2 有条件排名 =SUMPRODUCT((D2 |
【本文地址】
今日新闻 |
推荐新闻 |